package weibo4j.database;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.sql.Connection;

public class DataBaseUtil {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://172.16.8.26/";
	private static final String database = "sinamb";
	private static final String user = "lr";
	private static final String passwd = "apexlab";

	private static LinkedList<Connection> conns = new LinkedList<Connection>();
	public static Connection getDefaultConnection(){
		Connection conn = null;
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(url + database, user, passwd);
			conns.add(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	public static Connection getConnection(String _url, String _database,
			String _user, String _passwd) {
		Connection conn = null;
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://172.16.8.26/"
					+ _database, _user, _passwd);
			conns.add(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void close(Connection conn) throws SQLException{
		conn.close();
	}
	
	public static void clear() throws SQLException{
		for(Connection conn : conns){
			conn.close();
		}
	}
	
	
	public static List<String> getTablePK(Connection conn, String tableName) throws Exception {    
		List<String> result = new ArrayList<String>();   
 
		DatabaseMetaData dbmd = conn.getMetaData();   
		ResultSet rs = dbmd.getPrimaryKeys(null,null, tableName);     
		while (rs.next())  {   
			result.add(rs.getString("COLUMN_NAME"));  
		}
		if(result.size() > 0)
			return result;
		else
			return null;
	}  

	// without parameters
	public static boolean executeStatement(Connection conn, String sql) {
		Statement stmt = null;
		boolean ret = false;
		try {
			stmt = conn.createStatement();
			ret = stmt.execute(sql);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) { // ignore }
				}
				stmt = null;
			}
		}
		return ret;
	}
	
	public static boolean insert(Connection conn, String table, String filedString, String emptyValueString, String[] record) throws Exception {
		int count = record.length;
		if (count <= 0) {
			return true;
		}
		
		
		String sql = "insert into " + table + filedString + " values " + emptyValueString;
		PreparedStatement pstmt = conn.prepareStatement(sql);
		int idx = 1;
		while(idx <= count){
			pstmt.setString(idx, record[idx - 1]);
			idx ++;
		}
		int ret = pstmt.executeUpdate();
		pstmt.close();
		return ret > 0 ;
	}

	public static boolean insert(Connection conn, String table, Map<String, String> record) throws SQLException {
		int count = 0;
		if (record.size() <= 0) {
			return true;
		}
		
		StringBuffer sql = new StringBuffer("insert into " + table);
		String values[] = new String[record.size()];
		sql.append("(");
		Iterator<Map.Entry<String, String>> it = record.entrySet().iterator();
		while (it.hasNext()) {
			if(count != 0){
				sql.append(",");
			}
			Map.Entry<String, String> m = (Map.Entry<String, String>) it.next();
			sql.append(m.getKey());
			values[count] = m.getValue();
			count++;
		}
		sql.append(") values (");
		
		int idx = 0;
		while(idx < count){
			if(idx != 0){
				sql.append(",");
			}
			sql.append("?");
			idx++;
		}
		sql.append(")");
		
		PreparedStatement pstmt = conn.prepareStatement(sql.toString());
		idx = 1;
		while(idx <= count){
			pstmt.setString(idx, values[idx - 1]);
			idx ++;
		}
		int ret = pstmt.executeUpdate();
		return ret > 0 ;		

		/*
		StringBuffer fields = new StringBuffer();		
		StringBuffer values = new StringBuffer();
		fields.append("(");
		values.append("(");
		Iterator<Map.Entry<String, String>> it = record.entrySet().iterator();
		while (it.hasNext()) {
			if(count != 0){
				count = -1;
				fields.append(",");
				values.append(",");
			}
			Map.Entry<String, String> m = (Map.Entry<String, String>) it.next();
			fields.append(m.getKey());
			values.append(m.getValue());
			count++;
		}
		fields.append(")");
		values.append(")");
		
		String sql = "insert into " + table + " " + fields.toString() + " values " + values.toString();
		System.out.println(sql);
		Statement stmt = null;
		stmt = conn.createStatement();
		int ret = stmt.executeUpdate(sql);
		return ret > 0 ;*/
	}

	public static void main(String[] args) throws SQLException {
		Connection conn = DataBaseUtil.getConnection(null, "sinamb", "lr",
				"apexlab");

		String sql = "select count(*) as cnt from test";
		DataBaseUtil.executeStatement(conn, sql);
		conn.close();
	}
}
